package tlcn.business;

import java.sql.Date;
import java.util.ArrayList;
import tlcn.data.DateMenu;
import tlcn.data.Dish;
import tlcn.data.Material;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;

public class ManagerData {
	Context context;
	OpenHelper helper;
	SQLiteDatabase database;
	public ManagerData(Context context) {
		this.context = context;
		helper= new OpenHelper(context);
	}
	
	public ArrayList<Material> getShopping(String where)
	{
		ArrayList<Material> ma= new ArrayList<Material>();
		database= helper.getReadableDatabase();
		String sql=
				"Select  Materials.MaterialID,MaterialName,SUM(Weight)as totalWeight,Unit "+
				"From Setup join MaterialsOfDish on Setup.DishID=MaterialsOfDish.DishID "+
				"join Materials on Materials.MaterialID=MaterialsOfDish.MaterialID "+
				"Where "+where+" "+
				"GROUP BY Materials.MaterialID";
		Cursor c= database.rawQuery(sql, null);
		while (c.moveToNext()) {
			String materialID= c.getString(c.getColumnIndex("MaterialID"));
			String materialName= c.getString(c.getColumnIndex("MaterialName"));
			String unit= c.getString(c.getColumnIndex("Unit"));
			int weight= c.getInt(c.getColumnIndex("totalWeight"));
			ma.add(new Material(materialID, materialName, unit, weight));
		}
		database.close();
		return ma;
	}
	public boolean checkSetup(String date,String dishID,String meal)
	{
		database= helper.getReadableDatabase();
		String sql="SELECT * FROM Setup WHERE Date='"+date+"' AND DishID='"+dishID+"' AND Meal='"+meal+"'"; 
		if(database.rawQuery(sql,null).moveToNext())
			return false;
		return true;
	}
	public boolean addSetup(String date,String dishID,String meal){
		database= helper.getWritableDatabase();
		ContentValues values= new ContentValues();
		values.put("Date",date);
		values.put("DishID",dishID);
		values.put("Meal",meal);
		if (database.insert("Setup", null, values)!=-1)
			return true;
		return false;
	}
	public boolean removeDishOfDate(String date,String dishID,String meal){
		database=helper.getWritableDatabase();
		if (database.delete("Setup","Date='"+date+"' and DishID='"+dishID+"' and Meal='"+meal+"'",null)>0)
			return true;
		return false;
	}
	
	public ArrayList<DateMenu> getDishofDate(String date){
		ArrayList<DateMenu> list= new ArrayList<DateMenu>();
		database= helper.getReadableDatabase();
		String sql=
			"select Setup.DishID,DishName,Guide,Video,Meal "+
			"from Setup join Dishes on Setup.DishID=Dishes.DishID "+
			"where Date='"+date+"' "+
			"ORDER BY Meal";
		Cursor c= database.rawQuery(sql, null);
		while (c.moveToNext()) {
			DateMenu m = new DateMenu();
			m.setDate(Date.valueOf(date));
			m.setDish(new Dish(c.getString(0),c.getString(1),c.getString(2),c.getString(3), null));
			m.setMeal(c.getString(4));
			list.add(m);
		}
		database.close();
		return list;
	}
	
	public ArrayList<Material> getMaterialOfDish(String DishID){
		ArrayList<Material> materials= new ArrayList<Material>();
		database= helper.getReadableDatabase();
		String sql=
			"select Materials.MaterialID,MaterialName,Unit,Weight "+
			"from Materials join MaterialsOfDish on Materials.MaterialID=MaterialsOfDish.MaterialID "+
			"where DishID='"+DishID+"'";
		Cursor c= database.rawQuery(sql, null);
		while (c.moveToNext()) {
			String materialID= c.getString(c.getColumnIndex("MaterialID"));
			String materialName= c.getString(c.getColumnIndex("MaterialName"));
			String unit= c.getString(c.getColumnIndex("Unit"));
			int weight= c.getInt(c.getColumnIndex("Weight"));
			materials.add(new Material(materialID, materialName, unit, weight));
		}
		database.close();
		return materials;
	}
	
	public ArrayList<Material> getMaterialByName(String name){
		ArrayList<Material> materials= new ArrayList<Material>();
		database= helper.getReadableDatabase();
		String sql=
			"select *"+
			"from Materials "+
			"where MaterialName like '%"+name+"%'";
		Cursor c= database.rawQuery(sql, null);
		while (c.moveToNext()) {
			String materialID= c.getString(c.getColumnIndex("MaterialID"));
			String materialName= c.getString(c.getColumnIndex("MaterialName"));
			String unit= c.getString(c.getColumnIndex("Unit"));
			materials.add(new Material(materialID, materialName, unit,1));
		}
		database.close();
		return materials;
	}
	
	public ArrayList<Dish> getDishes(){
		ArrayList<Dish> dishes= new ArrayList<Dish>();
		database= helper.getReadableDatabase();
		Cursor c= database.query("Dishes", null, null,null,null, null, null, null);
		while (c.moveToNext()) {
			String id= c.getString(c.getColumnIndex("DishID"));
			String name= c.getString(c.getColumnIndex("DishName"));
			String guide= c.getString(c.getColumnIndex("Guide"));
			String video= c.getString(c.getColumnIndex("Video"));
			String type= c.getString(c.getColumnIndex("Type"));
			Bitmap bmp = null;
			byte[] img = c.getBlob(c.getColumnIndex("Image"));
			if (img != null)
				bmp = BitmapFactory.decodeByteArray(img, 0, img.length);
			Dish d=new Dish(id, name, guide, video, type);
			d.setImg(bmp);
			dishes.add(d);
		}
		database.close();
		return dishes;
	}
	
	public ArrayList<Dish> getDishesByName(String namedish){
		ArrayList<Dish> dishes= new ArrayList<Dish>();
		database= helper.getReadableDatabase();
		String sql=
				"select *"+
				"from Dishes "+
				"where NameSearch like '%"+namedish+"%'";
		Cursor c= database.rawQuery(sql, null);
		while (c.moveToNext()) {
			String id= c.getString(c.getColumnIndex("DishID"));
			String name= c.getString(c.getColumnIndex("DishName"));
			String guide= c.getString(c.getColumnIndex("Guide"));
			String video= c.getString(c.getColumnIndex("Video"));
			String type= c.getString(c.getColumnIndex("Type"));
			Bitmap bmp = null;
			byte[] img = c.getBlob(c.getColumnIndex("Image"));
			if (img != null)
				bmp = BitmapFactory.decodeByteArray(img, 0, img.length);
			Dish d=new Dish(id, name, guide, video, type);
			d.setImg(bmp);
			dishes.add(d);
		}
		database.close();
		return dishes;
	}
	
	

}
